package cn.hello2018.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

import cn.hello2018.entity.Profit;
import cn.hello2018.jdbc.DBUtil;

public class Service {
	private Connection conn;
	private Statement st, st1, st2; // 执行sql
	private ResultSet rs, rs1, rs2; // 获取sql执行的结果集
	private String sql; // sql语句
	private List<Profit> list;

	private Profit pf;

	public List<Profit> getProfit() {
		list = new ArrayList<>();
		conn = DBUtil.getConn();
		try {
			st = (Statement) conn.createStatement();
			st1 = (Statement) conn.createStatement();
			st2 = (Statement) conn.createStatement();

			sql = " SELECT DISTINCT g.goods_name goodsName, g.selling_price sellingPrice, g.cost_price costPrice, g.goods_id goodsId"
					+ " FROM report_goods g, report_trading t" + " WHERE t.trading_goods_id = g.goods_id";

			rs = st.executeQuery(sql); // 执行sql语句

			double profits = 0;// 商品利润

			while (rs.next()) {
				pf = new Profit();
				pf.setGoodsName(rs.getString("goodsName"));
				pf.setSellingPrice(rs.getDouble("sellingPrice"));
				pf.setCostPrice(rs.getDouble("costPrice"));
				pf.setGoodsId(rs.getInt("goodsId"));
				profits = 0;
				profits = pf.getCostPrice() - pf.getSellingPrice();

				sql = " SELECT SUM(t.trading_number) sumNum" + " FROM trading_information t"
						+ " WHERE t.trading_goods id = " + pf.getGoodsId();
				rs1 = st1.executeQuery(sql);
				while (rs1.next()) {
					pf.setTradingNum(rs1.getInt("sumNum"));
				}

				pf.setProfit(profits * pf.getTradingNum());

				sql = " SELECT COUNT(t.trading_id) times" + " FROM trading_information t"
						+ " WHERE t.trading_goods id = " + pf.getGoodsId();
				rs2 = st2.executeQuery(sql);
				while (rs2.next()) {
					pf.setTime(rs1.getInt("times"));
				}
			}
			list.add(pf);
			pf = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
}
